﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
/// <summary>
/// Summary description for Service
/// </summary>
public class Service
{
    SqlConnection cn;
	public Service()
	{
        cn = new SqlConnection();
        cn.ConnectionString = @"Data Source=(LocalDB)\v11.0;AttachDbFilename='C:\Users\LaiDuyHoang\Documents\Visual Studio 2012\Projects\qlksk17tpm\qlbh\qlbh\App_Data\qlks.mdf';Integrated Security=True";
	}
    public void Open() 
    {
        if (cn.State == ConnectionState.Closed)
        {
            cn.Open();
        }
    }
    public void Close()
    {
        if (cn.State == ConnectionState.Open)
        {
            cn.Close();
        }
    }
    public void ExeCuteSQL(string SQL)
    {
        try
        {
            this.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = SQL;
            cmd.CommandType = CommandType.Text;
            cmd.Connection = this.cn;
            cmd.ExecuteNonQuery();
        }
        catch (SqlException ex)
        {
            this.Close();
        }
    }
    public DataTable GetDataTable(string SQL) 
    {
        DataTable tb = new DataTable();
        try
        {
            this.Open();
            SqlDataAdapter adp = new SqlDataAdapter(SQL,this.cn);
            adp.Fill(tb);
        }
        catch (SqlException ex)
        {
            this.Close();
        }
        finally 
        {
            this.Close();
        }
        return tb;
    }
}